package com.edu.modules.baseinfo.dao;

import com.alibaba.fastjson.JSONObject;
import com.edu.modules.baseinfo.model.HomeWork;
import com.edu.modules.baseinfo.model.Student;
import com.edu.modules.entity.THomeworkUrlEntity;
import com.edu.modules.entity.TReviewedHomeworkUrlEntity;
import com.sari.common.persistence.BaseDao;
import com.sari.common.utils.DateUtils;
import com.sari.common.utils.IdGen;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created by lab on 2016/7/15.
 */
@Repository
public class HomeWorkDao extends BaseDao {

	public List<HomeWork> getHomeWorkList(String studentId){

		String sql = "select a.homework_id,a.homework_name,a.homework_url,a.score,a.comment," +
				"a.create_time,a.update_time,b.lesson_name,b.lesson_class from t_homework a, " +
				"t_lesson b where a.lesson_id = b.lesson_id and a.student_id = ?";

		return super.findList(sql,HomeWork.class,studentId);
	}

	public List getStudentHomeworkList(String studentId){

		String sql = "SELECT d.homework_id,e.student_homework_id,d.lesson_class,d.lesson_name," +
				"d.homework_subject,d.create_time FROM (SELECT b.lesson_class,b.lesson_name," +
				"c.homework_subject,c.create_time,c.homework_id FROM t_sign_up a,t_lesson b,t_homework c " +
				"WHERE a.lesson_id = b.lesson_id AND a.lesson_id = c.lesson_id AND a.is_paid = 1 AND a.student_id = ?) AS d " +
				"LEFT JOIN t_student_homework e ON d.homework_id = e.homework_id AND e.student_id =? ORDER BY d.create_time DESC";

		return super.listBySql(sql,studentId,studentId);
	}

	public List getTitleUrlList(String homeworkId) {

		String sql = "select homework_url from t_homework_url where url_type = 'teacher' and homework_id = ?";

		return super.listBySql(sql,homeworkId);
	}

	public List getHomeworkStudentUrlList(String studentId,String homeworkId){

		String sql = "select b.homework_url from t_student_homework a join t_homework_url b on " +
				"a.student_homework_id = b.homework_id where b.url_type = 'student' and a.student_id = ? " +
				"and a.homework_id = ?";

		return super.listBySql(sql,studentId,homeworkId);
	}

	public List getReviedHomeworkUrlList(String studentId,String homeworkId){

		String sql = "select url as homework_url from t_reviewed_homework_url where student_id = ? and student_homework_id = ?";

		return super.listBySql(sql,studentId,homeworkId);
	}

	public Map getHomeWorkDetile(String studentId, String homeworkId) {

		String hql = "select * from t_student_homework where homework_id = ? and student_id = ?";

		return getObjectMap(hql,homeworkId,studentId);
	}

	public List getTeacherHomeWorkByPage(String lessonName, String lessonClass,
										 String teacherName, int offset, int limit){

		String sql = "SELECT * FROM (SELECT a.homework_id,a.homework_subject,a.create_time,b.lesson_class," +
				"b.lesson_name,c.teacher_name FROM t_homework a,t_lesson b,t_teacher c WHERE a.teacher_id = c.teacher_id " +
				"AND b.lesson_id = a.lesson_id) AS d where 1=1";
		if(!lessonName.equals("")){
			sql += " and d.lesson_name like '%"+lessonName+"%'";
		}
		if(!teacherName.equals("")){
			sql += " and d.teacher_name like '%"+teacherName+"%'";
		}
		if(!lessonClass.equals("")){
			sql += " and d.lesson_class like '%"+lessonClass+"%'";
		}
		sql +=" ORDER BY d.create_time desc limit ?,?";

		return super.listBySql(sql,offset,limit);

	}

	public int getTeacherHomeWorkCounts(String lessonName,String lessonClass,String teacherName){

		String sql = "SELECT count(*) FROM (SELECT a.homework_id,a.homework_subject,a.create_time,b.lesson_class," +
				"b.lesson_name,c.teacher_name FROM t_homework a,t_lesson b,t_teacher c WHERE a.teacher_id = c.teacher_id " +
				"AND b.lesson_id = a.lesson_id) AS d where 1=1";
		if(!lessonName.equals("")){
			sql += " and d.lesson_name like '%"+lessonName+"%'";
		}
		if(!teacherName.equals("")){
			sql += " and d.teacher_name like '%"+teacherName+"%'";
		}
		if(!lessonClass.equals("")){
			sql += " and d.lesson_class like '%"+lessonClass+"%'";
		}

		return super.listSqlCount(sql);
	}

	public List getStudentHomeWorkByPage(String studentName,String lessonName, String lessonClass,
										 String teacherName,String lessonStartTime,String reviewStatus,int offset, int limit){

		String sql = "SELECT * FROM (SELECT d.student_homework_id,a.homework_id,d.student_id,a.homework_subject,d.create_time,b.lesson_class,b.lesson_start_time," +
				"b.lesson_end_time,b.week,b.lesson_term,b.lesson_name,c.teacher_name,d.score,e.student_name FROM t_homework a,t_lesson b,t_teacher c," +
				"t_student_homework d,t_student e WHERE a.teacher_id = c.teacher_id AND b.lesson_id = a.lesson_id AND a.homework_id = d.homework_id AND" +
				" e.student_id = d.student_id) AS m WHERE 1=1";
		if(!lessonName.equals("")){
			sql += " and m.lesson_name like '%"+lessonName+"%'";
		}
		if(!studentName.equals("")){
			sql += " and m.student_name like '%"+studentName+"%'";
		}
		if(!lessonClass.equals("")){
			sql += " and m.lesson_class like '%"+lessonClass+"%'";
		}
		if(!teacherName.equals("")){
			sql += " and m.teacher_name like '%"+teacherName+"%'";
		}
		if(!lessonStartTime.equals("")){
			sql += " and m.lesson_start_time like '%" +lessonStartTime+"%'";
		}
		if(reviewStatus.equals("0")){
			sql += " and m.score is null";
		}else if(reviewStatus.equals("1")){
			sql += " and m.score is not null";
		}

		sql +=" ORDER BY m.create_time desc limit ?,?";

		return super.listBySql(sql,offset,limit);
	}

	public int getStudentHomeWorkCounts(String studentName,String lessonName,String lessonClass,String teacherName,String lessonStartTime,String reviewStatus){

		String sql = "SELECT COUNT(*) FROM (SELECT d.student_homework_id,a.homework_id,a.homework_subject,d.create_time,b.lesson_class,b.lesson_start_time," +
				"b.lesson_end_time,b.week,b.lesson_term,b.lesson_name,c.teacher_name,d.score,e.student_name FROM t_homework a,t_lesson b,t_teacher c," +
				"t_student_homework d,t_student e WHERE a.teacher_id = c.teacher_id AND b.lesson_id = a.lesson_id AND a.homework_id = d.homework_id AND" +
				" e.student_id = d.student_id) AS m WHERE 1=1";
		if(!lessonName.equals("")){
			sql += " and m.lesson_name like '%"+lessonName+"%'";
		}
		if(!studentName.equals("")){
			sql += " and m.student_name like '%"+studentName+"%'";
		}
		if(!lessonClass.equals("")){
			sql += " and m.lesson_class like '%"+lessonClass+"%'";
		}
		if(!teacherName.equals("")){
			sql += " and m.teacher_name like '%"+teacherName+"%'";
		}
		if(!lessonStartTime.equals("")){
			sql += " and m.lesson_start_time like '%" +lessonStartTime+"%'";
		}
		if(reviewStatus.equals("0")){
			sql += " and m.score is null";
		}else if(reviewStatus.equals("1")){
			sql += " and m.score is not null";
		}

        return super.listSqlCount(sql);
	}


	public void updateStudentHomeWork(String studentHomeworkId,String score,
							  String comment,String reviewUrl,String updateTime){

		String sql = "update t_student_homework set score = ?,comment = ?,review_url = ?,update_time = ? where student_homework_id = ?";

		executeSql(sql,score,comment,reviewUrl,updateTime,studentHomeworkId);
	}

	public List getStudentHomeworkUrl(String homeworkId) {

		String sql = "select homework_url from t_homework_url where url_type = 'student' and homework_id = ?";

		return listBySql(sql,homeworkId);
	}

	public List getTeacherHomeworkUrl(String homeworkId) {

		String sql = "select homework_url from t_homework_url where url_type = 'teacher' and homework_id = ?";

		return listBySql(sql,homeworkId);
	}

	public void saveStudentHomeworkUrl(String homeworkId,String imgUrl,String dateTime,String type){

		String[] imgUrls=imgUrl.split(",");
		for (int i=0;i<imgUrls.length;i++){
			THomeworkUrlEntity homeworkUrlEntity = new THomeworkUrlEntity();
			homeworkUrlEntity.setUrlType(type);
			homeworkUrlEntity.setHomeworkId(homeworkId);
			homeworkUrlEntity.setHomeworkUrl("/homework/"+imgUrls[i]);
			homeworkUrlEntity.setCreateTime(dateTime);
			super.saveObject(homeworkUrlEntity);
		}
	}

	public void saveReviewedHomeworkUrl(String studentHomeworkId,String url,String studentId){

		String[] imgUrls=url.split(",");
		for (int i=0;i<imgUrls.length;i++){

			TReviewedHomeworkUrlEntity reviewedHomeworkUrl = new TReviewedHomeworkUrlEntity();
			reviewedHomeworkUrl.setStudentId(studentId);
			reviewedHomeworkUrl.setStudentHomeworkId(studentHomeworkId);
			reviewedHomeworkUrl.setUrl("/homework/"+imgUrls[i]);
			reviewedHomeworkUrl.setCreateTime(DateUtils.getDateTime());
			super.saveObject(reviewedHomeworkUrl);
		}
	}

	public int saveStudentHomework(String studentHomeworkId,String studentId,String homeworkId,String dateTime){

		String sql = "insert into t_student_homework (student_homework_id,student_id,homework_id,create_time) values(?,?,?,?)";

		return saveOrUpdateOrDelete(sql, studentHomeworkId,studentId,homeworkId,dateTime);
	}

}
